﻿Option Explicit On

Imports System.Text


Public Class Company
    Public Enum CompanyRoles
        Candidate = 1
        Employee = 4
        ExEmployee = 5
        Director = 6
        Contact = 7
        HRDirector = 8
        HRManager = 9
        HRAssistant = 10
        HRGeneralist = 11
        FinanceManager = 12
        VicePresident = 13
    End Enum

    Public Structure CorporateContact
        Public ContactID As Long
        Public ContactName As String
    End Structure
    Public Enum CompanyPeopleView
        All = 1
        Candidates = 2
        Contacts = 3
        Category = 4
    End Enum
    Public Structure CompanyInfo
        Public CompanyID As Long
        Public CompanyName As String
        Public IndustryName As String
        Public IndustryId As Integer
        Public CompanySize As String
        Public CompanySizeId As Integer
        Public DateEntered As Date
        Public Website As String
        Public EnteredBy As String
        Public AccountManagerId As Integer
        Public AccountManager As String
        Public DefaultCaseWorker As String
        Public DefaultCaseWorkerId As Integer
        Public AccountType As String
        Public AccountTypeId As Integer
        Public CompanyStatusId As Integer
        Public CompanyStatus As String
        Public WhereHeard As String
        Public ParentCompanyId As Long
        Public CompanyCoordinatorID As Long
        Public CompanyCoordinatorName As String
    End Structure

    Public Enum AddressLinkTypes
        NotSet = 0
        Correspondence = 1
        Invoice = 2
        Both = 3
    End Enum

    Public Shared Function GetCompany(ByVal CompanyId As Long) As CompanyInfo

        Dim myData As DataTable
        Dim dataAccess As New CRM.DataLayer.DataAccess
        GetCompany = Nothing

        If CompanyId = 0 Then
            Exit Function
        End If
        Dim sql As New StringBuilder
        With sql
            .Append("select distinct c1.company_id,c1.company_name, i.industry_name,cs.companysize_name,Convert(varchar(40),c1.company_dtentered,103) as company_dtentered,c1.company_website,")
            .Append(" c1.company_enteredby,con2.con_name as accountman,con3.con_name as caseworker,acc.accounttype_name, st.companystatus_name,c1.company_industryid,c1.company_sizeid,")
            .Append(" c1.company_accountmanagerid,c1.company_caseworkerid,c1.company_accounttype,c1.company_statusid,c1.company_parentid, con3.Con_Name, ")
            .Append(" c1.Company_CoordinatorID, con1.Con_Name as Company_CoordinatorName from d_company c1   ")
            .Append(" left outer join l_accounttypes acc on c1.company_accounttype = acc.accounttype_id left outer join l_companystatus st on ")
            .Append(" c1.company_statusid =st.companystatus_id  left outer join l_industry i on c1.company_industryid = i.industry_id  ")
            .Append(" left outer join l_companysize cs on c1.company_sizeid = cs.companysize_id  left outer join l_Consultant con1 on c1.Company_CoordinatorID = con1.Con_ID  ")
            .Append(" left outer join l_Consultant con2 on c1.Company_AccountManagerID = con2.Con_ID left outer join l_Consultant con3 on c1.Company_CaseworkerID = con3.Con_ID")
            .Append(" where(c1.company_id = " & CompanyId & ")")
        End With
        myData = dataAccess.GetSQLDataTable(sql.ToString())
        'myData = dataAccess.GetSQLDataTable("CRM3_GetCompanyData " & CompanyId)


        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then

                Dim myInfo As New CompanyInfo
                Dim myRow As DataRow = myData.Rows(0)
                Dim dtEnt As Object
                With myInfo
                    .CompanyID = myRow.Item("company_id")
                    .CompanyName = IIf(IsDBNull(myRow.Item("company_name")), "", myRow.Item("company_name"))
                    .CompanySize = IIf(IsDBNull(myRow.Item("companysize_name")), "", myRow.Item("companysize_name"))
                    .CompanySizeId = myRow.Item("company_sizeid")
                    dtEnt = IIf(IsDBNull(myRow.Item("company_dtentered")), Nothing, myRow.Item("company_dtentered"))

                    If Not dtEnt Is Nothing Then
                        If IsDate(dtEnt) = True Then
                            .DateEntered = CDate(dtEnt)
                        Else
                            .DateEntered = Nothing
                        End If

                    Else
                        .DateEntered = Nothing
                    End If
                    .EnteredBy = IIf(IsDBNull(myRow.Item("company_enteredby")), "", myRow.Item("company_enteredby"))
                    .IndustryId = myRow.Item("company_industryid")
                    .IndustryName = IIf(IsDBNull(myRow.Item("industry_name")), "", myRow.Item("industry_name"))
                    .Website = IIf(IsDBNull(myRow.Item("company_website")), "", myRow.Item("company_website"))
                    .AccountManagerId = myRow.Item("company_accountmanagerid")
                    .AccountManager = IIf(IsDBNull(myRow.Item("accountman")), "", myRow.Item("accountman"))
                    .DefaultCaseWorker = IIf(IsDBNull(myRow.Item("caseworker")), "", myRow.Item("caseworker"))
                    .DefaultCaseWorkerId = myRow.Item("company_caseworkerid")
                    .AccountTypeId = myRow.Item("company_accounttype")
                    .AccountType = IIf(IsDBNull(myRow.Item("accounttype_name")), "", myRow.Item("accounttype_name"))
                    .CompanyStatus = IIf(IsDBNull(myRow.Item("companystatus_name")), "", myRow.Item("companystatus_name"))
                    .CompanyStatusId = myRow.Item("company_statusid")
                    .WhereHeard = CommonDataFunctions.GetWhereHeard(CompanyId, CommonDataFunctions.AdvertLinkType.Company)
                    .ParentCompanyId = myRow.Item("company_parentid")
                    .CompanyCoordinatorID = myRow.Item("Company_CoordinatorID")
                    .CompanyCoordinatorName = IIf(IsDBNull(myRow.Item("Company_CoordinatorName")), "", myRow.Item("Company_CoordinatorName"))
                End With
                GlobalCRM.CurrentCompanyName = myInfo.CompanyName
                Return myInfo

            End If

        End If


    End Function


    Public Shared Function SimilarCompanies(ByVal CompanyName As String) As DataTable

        'Check for similar spelled companies so that duplicate companies aren't added
        Dim cmn As New CommonFuncs
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try

            Dim SQL As New StringBuilder

            With SQL
                .Append("Select Company_ID, Company_Name, coalesce(Industry_Name,'') as  Industry_Name")
                .Append(" FROM  D_Company LEFT OUTER JOIN L_Industry ON D_Company.Company_IndustryID = L_Industry.Industry_ID")
                .Append(" where Company_Name like '%" & cmn.SwapQuotes(CompanyName.Trim) & "%' ")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            cmn = Nothing
            dataAccess = Nothing
        End Try

    End Function

    Public Shared Function SimilarCompaniesFromID(ByVal CompanyID As Long) As DataTable

        'Check for similar spelled companies so that duplicate companies aren't added

        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try

            Dim SQL As New StringBuilder

            With SQL
                .Append("Select Company_ID, Company_Name, coalesce(Industry_Name,'') as industry ")
                .Append(" FROM  D_Company LEFT OUTER JOIN L_Industry ON D_Company.Company_IndustryID = L_Industry.Industry_ID")
                .Append(" where Company_ID = " & CompanyID)
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function AddCompany(ByVal CompData As CompanyInfo, ByVal OldCompData As CompanyInfo) As Long
        Dim cmn As New CommonFuncs
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim Tsk As New TasksEvents
        Dim SQL As New StringBuilder
        Dim intCount As Integer

        Try


            If CompData.CompanyID = 0 Then

                With SQL
                    .Append("Insert Into D_Company (Company_Name, Company_IndustryID,Company_sizeID, Company_EnteredBy, Company_DTEntered, Company_AccountManagerID, company_caseworkerid, Company_StatusID, company_website, company_CoordinatorID)")
                    .Append(" VALUES('" & cmn.SwapQuotes(CompData.CompanyName.Trim) & "'," & CompData.IndustryId & ",")
                    .Append(CompData.CompanySizeId & ",'" & GlobalCRM.CurrentConName & "','" & cmn.FormatDate(Now, True) & "',")
                    .Append(GlobalCRM.CurrentConID & ",11," & CompData.CompanyStatusId & ",'")
                    .Append(cmn.SwapQuotes(IIf(IsNothing(CompData.Website), "", CompData.Website)) & "'," & CStr(CompData.CompanyCoordinatorID) & ")")
                End With


                Return dataAccess.ExecuteSQL(SQL.ToString, True)
                'Return dataAccess.GetIdentity
                'If cid > 0 Then
                '    dataAccess.CommitTrans()
                'Else
                '    dataAccess.RollbackTrans()
                'End If
                SQL = Nothing
                SQL = New StringBuilder

                With SQL
                    .Append("Insert into  D_Advertising (Advertising_WhereHear, Advertising_Enteredby, Advertising_DTEntered, Advertising_LinkId, Advertising_LinkType)")
                    .Append(" Values ( '" & cmn.SwapQuotes(CompData.WhereHeard.Trim) & "', ")
                    .Append(" '" & GlobalCRM.CurrentConName & "', ")
                    .Append(" '" & Now() & "', ")
                    .Append(" " & CStr(CompData.CompanyID) & ", 1) ")
                    '.Append(" Where Advertising_LinkType = 1 ")
                    '.Append(" And Advertising_LinkID = " & CompData.CompanyID)
                End With

                If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If


            Else
                'Edit mode
                Dim Dirty As Boolean
                Dim Heard As Boolean
                Dim Msg As New StringBuilder

                With SQL

                    .Append("Update D_Company set ")

                    If StrComp(CompData.CompanyName, OldCompData.CompanyName, 0) <> 0 Then
                        Dirty = True
                        .Append(" Company_Name = '" & cmn.SwapQuotes(CompData.CompanyName.Trim) & "', ")
                        Dim oldCompanyName As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.CompanyName) = False) Then
                            oldCompanyName = OldCompData.CompanyName.Trim
                        End If
                        Msg.Append("Company name changed from " & oldCompanyName & " to " & CompData.CompanyName.Trim & vbCrLf)
                    End If

                    If CompData.WhereHeard <> OldCompData.WhereHeard Then
                        Heard = True
                        Dim oldCompanyWhereHeard As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.WhereHeard) = False) Then
                            oldCompanyWhereHeard = OldCompData.WhereHeard.Trim
                        End If
                        Msg.Append("Where Heard changed from " & oldCompanyWhereHeard & " to " & CompData.WhereHeard & vbCrLf)
                    End If

                    If CompData.IndustryId <> OldCompData.IndustryId Then
                        Dirty = True
                        .Append(" Company_IndustryID = " & CompData.IndustryId & ", ")
                        Dim oldCompanyIndustryName As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.IndustryName) = False) Then
                            oldCompanyIndustryName = OldCompData.IndustryName.Trim
                        End If
                        Msg.Append("Company industry changed from " & oldCompanyIndustryName & " to " & CompData.IndustryName.Trim & vbCrLf)
                    End If

                    If CompData.CompanySizeId <> OldCompData.CompanySizeId Then
                        Dirty = True
                        .Append(" Company_SizeID = " & CompData.CompanySizeId & ", ")
                        Dim oldCompanySize As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.CompanySize) = False) Then
                            oldCompanySize = OldCompData.CompanySize.Trim
                        End If
                        Msg.Append("Company size changed from " & oldCompanySize & " to " & CompData.CompanySize.Trim & vbCrLf)
                    End If

                    Dim oldCompanyWebsite As String = String.Empty
                    If (String.IsNullOrEmpty(OldCompData.Website) = False) Then
                        oldCompanyWebsite = OldCompData.Website.Trim
                    End If
                    If CompData.Website.Trim <> oldCompanyWebsite Then
                        Dirty = True
                        .Append(" Company_Website = '" & cmn.SwapQuotes(CompData.Website.Trim) & "', ")
                        Msg.Append("Website address changed from " & OldCompData.Website.Trim & " to " & CompData.Website.Trim & vbCrLf)
                    End If

                    If CompData.AccountManagerId <> OldCompData.AccountManagerId Then
                        Dirty = True
                        .Append(" Company_accountmanagerID = " & CompData.AccountManagerId & ", ")
                        Dim oldCompanyAccountManager As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.AccountManager) = False) Then
                            oldCompanyAccountManager = OldCompData.AccountManager.Trim
                        End If
                        Msg.Append("Company account manager changed from " & oldCompanyAccountManager & " to " & CompData.AccountManager.Trim & vbCrLf)
                    End If

                    If CompData.DefaultCaseWorkerId <> OldCompData.DefaultCaseWorkerId Then
                        Dirty = True
                        .Append(" Company_caseworkerID = " & CompData.DefaultCaseWorkerId & ", ")
                        Dim oldCompanyDefaultCaseWorker As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.DefaultCaseWorker) = False) Then
                            oldCompanyDefaultCaseWorker = OldCompData.DefaultCaseWorker.Trim
                        End If
                        Msg.Append("Company caseworker changed from " & oldCompanyDefaultCaseWorker & " to " & CompData.DefaultCaseWorker.Trim & vbCrLf)
                    End If

                    If CompData.AccountTypeId <> OldCompData.AccountTypeId Then
                        Dirty = True
                        .Append(" Company_accounttype = " & CompData.AccountTypeId & ", ")
                        Dim oldCompanyAccountType As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.AccountType) = False) Then
                            oldCompanyAccountType = OldCompData.AccountType.Trim
                        End If
                        Msg.Append("Company account type changed from " & oldCompanyAccountType & " to " & CompData.AccountType.Trim & vbCrLf)
                    End If


                    If CompData.CompanyStatusId <> OldCompData.CompanyStatusId Then
                        Dirty = True
                        .Append(" Company_StatusID = " & CompData.CompanyStatusId & ", ")
                        Dim oldCompanyStatus As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.CompanyStatus) = False) Then
                            oldCompanyStatus = OldCompData.CompanyStatus.Trim
                        End If
                        Msg.Append("Company status changed from " & oldCompanyStatus & " to " & CompData.CompanyStatus.Trim & vbCrLf)
                    End If

                    If CompData.CompanyCoordinatorID <> OldCompData.CompanyCoordinatorID Then
                        Dirty = True
                        .Append(" company_CoordinatorID = " & CompData.CompanyCoordinatorID & ", ")
                        Dim oldCompanyCoordinatorName As String = String.Empty
                        If (String.IsNullOrEmpty(OldCompData.CompanyCoordinatorName) = False) Then
                            oldCompanyCoordinatorName = OldCompData.CompanyCoordinatorName.Trim
                        End If
                        Msg.Append("Company Coordinator changed from " & oldCompanyCoordinatorName & " to " & CompData.CompanyCoordinatorName & vbCrLf)
                    End If

                    If Not Dirty And Not Heard Then
                        'Nothing changed
                        Return CompData.CompanyID
                        Exit Function
                    Else
                        If Dirty Then
                            .Remove(.Length - 2, 2)

                            Msg.Insert(0, "Company details updated, changes below." & vbCrLf & "------------" & vbCrLf, 1)

                            .Append(" Where company_id = " & CompData.CompanyID)

                            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                                Exit Function
                            End If

                        End If

                        If Heard Then


                            SQL = New StringBuilder
                            With SQL

                            End With
                            intCount = dataAccess.GetSQLScalar("Select count(*) as ctr from D_Advertising where Advertising_LinkType=1 and Advertising_LinkID= " & CompData.CompanyID)

                            If intCount = 0 Then
                                With SQL
                                    .Append("Insert into  D_Advertising (Advertising_WhereHear, Advertising_Enteredby, Advertising_DTEntered, Advertising_LinkId, Advertising_LinkType)")
                                    .Append(" Values ( '" & cmn.SwapQuotes(CompData.WhereHeard.Trim) & "', ")
                                    .Append(" '" & GlobalCRM.CurrentConName & "', ")
                                    .Append(" '" & Now.ToString("yyyy-MM-dd hh:mm:ss") & "', ")
                                    .Append(" " & CStr(CompData.CompanyID) & ", 1) ")
                                    '.Append(" Where Advertising_LinkType = 1 ")
                                    '.Append(" And Advertising_LinkID = " & CompData.CompanyID)
                                End With

                                If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                                    Exit Function
                                End If
                            Else
                                SQL = New StringBuilder

                                With SQL
                                    .Append("Update D_Advertising ")
                                    .Append(" Set Advertising_WhereHear = '" & cmn.SwapQuotes(CompData.WhereHeard.Trim) & "'")
                                    .Append(" Where Advertising_LinkType = 1 ")
                                    .Append(" And Advertising_LinkID = " & CompData.CompanyID)
                                End With

                                If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                                    Exit Function
                                End If
                            End If

                        End If

                        Tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, CompData.CompanyID, TasksEvents.LinkTypes.Company)

                        Return CompData.CompanyID

                    End If
                End With

            End If
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing
            cmn = Nothing
            Tsk = Nothing
        End Try
    End Function
    Public Overloads Shared Function SearchCompany(ByVal CompanyName As String) As DataTable

        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT Company_Id, Company_Name, Company_ParentID  FROM D_Company ")
                .Append("WHERE Company_name Like '%" & CompanyName & "%'")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing

        End Try
    End Function
    Public Overloads Shared Function SearchCompany(ByVal CompanyId As Long) As DataTable
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT Company_Id, Company_Name, Company_ParentID  FROM D_Company ")
                .Append("WHERE Company_id = " & CompanyId)
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing

        End Try
    End Function
    Public Shared Function SubsidiaryList(ByVal CompanyId As Long, Optional ByVal AddParent As Boolean = True) As Array

        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try
            Dim myData As DataTable
            Dim SubList() As Long = Nothing
            Dim i As Integer

            If AddParent Then
                ReDim Preserve SubList(i)
                SubList(i) = CompanyId
                i += 1
            End If

            myData = dataAccess.GetSQLDataTable("Select Company_ID from D_Company where Company_ParentID = " & CompanyId)

            If Not IsNothing(myData) Then

                Dim myRow As DataRow

                For Each myRow In myData.Rows
                    ReDim Preserve SubList(i)

                    SubList(i) = myRow.Item("company_id")
                    i += 1

                Next

            End If

            Return SubList
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function EmployeeList(ByVal CompanyId As Long) As Array

        'Get list of employees in company
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable
            Dim PeopleList() As Long = Nothing

            With SQL
                .Append("Select Person_ID ")
                .Append(" From D_Person Left Outer Join ")
                .Append(" L_Roles on D_Person.Person_CompanyRoleID = L_Roles.Role_ID")
                .Append(" Where Person_CompanyID = " & CompanyId)
                .Append(" And Role_Employee = 2")
            End With

            myData = dataAccess.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then

                Dim myRow As DataRow
                Dim i As Integer


                For Each myRow In myData.Rows

                    ReDim Preserve PeopleList(i)

                    PeopleList(i) = myRow.Item("person_id")

                    i += 1

                Next
            End If

            myData.Dispose()

            Return PeopleList
        Catch ex As Exception
            Return Nothing

        Finally

            dataAccess = Nothing
            Sql = Nothing

        End Try
    End Function

    Public Shared Function CompanyPeople(ByVal CompanyId As Long) As DataTable
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS person_name, L_Roles.Role_Name ")
                .Append("FROM D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID ")
                .Append("WHERE D_Person.Person_CompanyID = " & CompanyId)
                .Append(" order by person_name")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing
            SQL = Nothing

        End Try
    End Function

    Public Shared Function CompanyCandidates(ByVal CompanyId As Long) As DataTable

        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("Select Person_ID, Person_Fname + ' ' + Person_LName as PersonName")
                .Append(" From D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID")
                .Append(" WHERE Person_CompanyID = " & CompanyId)
                .Append(" AND Role_Employee in (0)")
                .Append(" order by personname")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing
            SQL = Nothing

        End Try
    End Function


    Public Shared Function CompanyContacts(ByVal CompanyId As Long) As DataTable
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("Select Person_ID, Person_Fname + ' ' + Person_LName as PersonName")
                .Append(" From D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID")
                .Append(" WHERE Person_CompanyID = " & CompanyId)
                .Append(" AND Role_Employee in (1,2)")
                .Append(" order by personname")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing
            SQL = Nothing

        End Try
    End Function



    Public Shared Function CompanyCases(ByVal CompanyId As Long) As DataTable

        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            If CompanyId = 0 Then
                Return Nothing
                Exit Function
            End If

            With SQL
                .Append("SELECT D_Case.Case_ID, L_Products.Product_Name, L_CaseStages.CaseStage_Title, ")
                .Append("convert(varchar(10),D_Case.Case_ID) + ' | ' + L_Products.Product_Name + ' | ' + L_CaseStages.CaseStage_Title as display_member ")
                .Append("FROM D_Case LEFT OUTER JOIN L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number ")
                .Append("LEFT OUTER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID ")
                .Append("WHERE D_Case.Case_CompanyID = " & CompanyId & " ORDER BY L_CaseStages.CaseStage_Number")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            dataAccess = Nothing
            SQL = Nothing

        End Try
    End Function

    Public Shared Function GetAccountType(ByVal CompanyId As Long) As Integer
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try

            Return dataAccess.GetSQLScalar("Select Company_AccountType from D_Company where Company_ID = " & CompanyId)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try

    End Function

    Public Shared Function GetStopStatus(ByVal CompanyId As Long) As Integer
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try

            Return dataAccess.GetSQLScalar("Select Company_StopStatus from D_Company where Company_ID = " & CompanyId)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try

    End Function

    Public Shared Function UpdateParent(ByVal CompanyId As Long, ByVal ParentId As Long) As Boolean
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim SQL As New StringBuilder
        Try

            With SQL
                .Append("Update D_Company set Company_ParentID = " & ParentId)
                .Append(" where Company_ID = " & CompanyId)
            End With

            Return dataAccess.ExecuteSQL(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
            Sql = Nothing
        End Try

    End Function

    Public Shared Function GetParentCompany(ByVal CompanyId As Long) As CompanyInfo
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try


            Dim ParentId As Long = dataAccess.GetSQLScalar("select company_parentid from d_company where company_id = " & CompanyId)

            If ParentId > 0 Then
                Return GetCompany(ParentId)
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
        End Try
    End Function

    Public Shared Function RemoveSubsidiary(ByVal CompanyId As Long, ByVal CompanyName As String, ByVal SubsidiaryId As Long, ByVal SubsidiaryName As String) As Boolean
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim Tsk As New TasksEvents

        Try
            Dim Msg As New StringBuilder
            Dim ParentComp As CompanyInfo = GetParentCompany(CompanyId)

            With Msg
                .Append("Company no. " & SubsidiaryId & " (" & SubsidiaryName.Trim & ")")
                .Append(" removed as subsidiary of " & CompanyId & " (" & CompanyName.Trim & ")")
            End With

            If Not dataAccess.ExecuteSQL("Update D_Company Set Company_ParentID = 0 where company_ID = " & SubsidiaryId) Then
                Exit Function
            End If

            Tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, 1, CompanyId, TasksEvents.EventTypes.EventCompany)
            Tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, 1, SubsidiaryId, TasksEvents.EventTypes.EventCompany)

            Return True
        Catch ex As Exception
        Finally
            dataAccess = Nothing
            Tsk = Nothing
        End Try
    End Function

    Public Shared Function HasInvoiceAddress(ByVal CompanyId As Long) As Boolean
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try


            Dim SQL As New StringBuilder

            With SQL
                .Append("Select count(*) from D_Addresslink")
                .Append(" Where Addresslink_CompanyId = " & CompanyId)
                .Append(" And Addresslink_Type in (2,3)")
            End With

            If dataAccess.GetSQLScalar(SQL.ToString) > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
        End Try
    End Function

    Public Shared Function GetCompanyRoleName(ByVal RoleId As Integer) As String
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Try


            Dim myRole As String

            myRole = dataAccess.GetSQLScalar("Select Role_Name from L_Roles where Role_ID = " & RoleId)

            If myRole.Trim = String.Empty Then
                Return "Unspecified"
            Else
                Return myRole.Trim
            End If
        Catch ex As Exception
            Return String.Empty
        Finally
            dataAccess = Nothing
        End Try
    End Function

    Public Shared Function SetCorrespOrInvAddress(ByVal AddressLinkId As Long, ByVal CompanyID As Long, ByVal AddressName As String, ByVal ChangeTo As AddressLinkTypes, ByVal ChangeFrom As AddressLinkTypes) As Boolean
        Dim dataAccess As New CRM.DataLayer.DataAccess
        Dim tsk As New TasksEvents
        Dim Msg As New StringBuilder
        Dim SQL As New StringBuilder

        Try
            'Flags an address as being used for correspondence and / or invoices

            'Set all addresses to appropriate type, this includes ones not selected
            With SQL
                Select Case ChangeTo
                    Case AddressLinkTypes.Both
                        .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 2 then 3 when 1 then 3 when 3 then 0 end ")
                        .Append("WHERE AddressLink_CompanyID = " & CompanyID & " AND AddressLink_Type in (1,2)")
                    Case AddressLinkTypes.Correspondence
                        .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 1 then 0 when 3 then 2 end ")
                        .Append("WHERE AddressLink_CompanyID = " & CompanyID & " AND AddressLink_Type in (1,3)")
                    Case AddressLinkTypes.Invoice
                        .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 2 then 0 when 3 then 1 end ")
                        .Append("WHERE AddressLink_CompanyID = " & CompanyID & " AND AddressLink_Type in (2,3)")
                End Select
            End With

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            'Set selected address to Corr, Inv or Both, depending on what it was originally
            If ChangeTo = AddressLinkTypes.Correspondence Then
                If ChangeFrom = AddressLinkTypes.Invoice Then
                    ChangeTo = AddressLinkTypes.Both
                    Msg.Append(AddressName.Trim & " set as both correspondence and invoice address")
                Else
                    ChangeTo = AddressLinkTypes.Correspondence
                    Msg.Append(AddressName.Trim & " set as correspondence address")
                End If
            ElseIf ChangeTo = AddressLinkTypes.Invoice Then
                If ChangeFrom = AddressLinkTypes.Correspondence Then
                    ChangeTo = AddressLinkTypes.Both
                    Msg.Append(AddressName.Trim & " set as both correspondence and invoice address")
                Else
                    ChangeTo = AddressLinkTypes.Invoice
                    Msg.Append(AddressName.Trim & " set as invoice address")
                End If
            End If

            SQL = New StringBuilder
            SQL.Append("Update D_AddressLink SET AddressLink_Type = " & ChangeTo & " WHERE AddressLink_ID = " & AddressLinkId)

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, CompanyID, TasksEvents.LinkTypes.Company)

            Return True
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
            SQL = Nothing
            Msg = Nothing
            tsk = Nothing
        End Try
    End Function
    Public Shared Function GetCompanyWhereHeard(ByVal CompanyId As Long) As String

        Dim db As New CRM.DataLayer.DataAccess

        Dim SQL As StringBuilder

        Try
            SQL = New StringBuilder
            With SQL
                .Append("Select top 1 advertising_wherehear from D_Advertising  Where Advertising_LinkType = 1 AND Advertising_LinkID = " & CompanyId)
                .Append(" order by advertising_dtentered")
                Return DB.GetSQLScalar(.ToString)
            End With
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            SQL = Nothing

        End Try
    End Function
    Public Shared Function GetCorporateAddresses(ByVal CompanyId As Long) As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("SELECT AddressLink_ID, AddressLink_Name, CASE AddressLink_Type WHEN 1 THEN 'Cor' WHEN 2 THEN 'Inv' WHEN 3 THEN 'Both' ELSE '' END as Type, left(D_Address.Address_Text,30) as FirstLine, AddressLink_Type FROM D_AddressLink INNER JOIN D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID WHERE (AddressLink_CompanyID = " & CompanyId & ")")
                '.Append("SELECT AddressLink_ID, AddressLink_Name, CASE AddressLink_Type WHEN 1 THEN 'Cor' WHEN 2 THEN 'Inv' WHEN 3 THEN 'Both' ELSE '' END as Type, left(D_Address.Address_Text,30) as FirstLine FROM D_AddressLink INNER JOIN D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID WHERE (AddressLink_CompanyID = " & CompanyId & ")")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing

        End Try
    End Function
    Public Shared Function GetCorporateContacts(ByVal CompanyID As Long) As DataTable


        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder


            With SQL
                .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Name, L_Roles.Role_Name FROM L_Roles INNER JOIN D_Person ON L_Roles.Role_ID = D_Person.Person_CompanyRoleID WHERE (L_Roles.Role_Employee = 1 OR L_Roles.Role_Employee = 2) AND (D_Person.Person_CompanyID = " & CompanyID & ")")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function GetCorporatePeople(ByVal CompanyId As Long, Optional ByVal RoleId As CompanyPeopleView = CompanyPeopleView.All, Optional ByVal RoleCategory As CompanyRoles = 0) As DataTable

        'Gets a list of all people associated with a company client

        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Name, L_Roles.Role_Name ")
                .Append("FROM L_Roles INNER JOIN D_Person ON L_Roles.Role_ID = D_Person.Person_CompanyRoleID ")
                .Append("WHERE (D_Person.Person_CompanyID = " & CompanyId & ") ")

                Select Case RoleId
                    Case CompanyPeopleView.Candidates
                        .Append("and person_companyroleid = " & CompanyRoles.Candidate & " ")

                    Case CompanyPeopleView.Category

                        .Append("and person_companyroleid = " & RoleCategory & " ")


                    Case CompanyPeopleView.Contacts
                        .Append("and person_companyroleid = " & CompanyRoles.Contact & " ")

                End Select

                .Append("ORDER BY  Person_Fname, Person_LName")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function GetCorporateContact(ByVal CaseID As Long) As CorporateContact

        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As New StringBuilder
        Dim myData As New DataTable
        Try

            SQL = New StringBuilder


            GetCorporateContact = Nothing

            With SQL
                .Append("select p.person_id,p.person_fname + ' ' + p.person_lname as contactname ")
                .Append("from d_person p, d_case c ")
                .Append("where c.case_id = " & CaseID)
                .Append(" and c.case_contactid = p.person_id and c.case_companyid > 0")
                myData = db.GetSQLDataTable(.ToString)

                If Not IsNothing(myData) Then
                    If myData.Rows.Count > 0 Then
                        Dim myInfo As CorporateContact
                        Dim myRow As DataRow = myData.Rows(0)

                        With myInfo
                            .ContactID = myRow.Item("person_id")
                            .ContactName = IIf(IsDBNull(myRow.Item("contactname")), "", myRow.Item("contactname"))
                        End With
                        Return myInfo
                    End If
                End If

            End With
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            myData = Nothing
            db = Nothing
        End Try
    End Function

    Public Shared Function CompanyRoleList() As DataTable
        Dim db As New CRM.DataLayer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("select role_id,role_name from l_roles order by role_name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
End Class
